package com.neuedu.xkxt.dao;

import com.neuedu.xkxt.model.ScoreDetails;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ScoreStatsMapper {

    /**
     * 统计每门课程的最高、最低、平均成绩、及格人数、不及格人数及方差
     */
    @Select("SELECT " +
            "    base.cid AS courseId, " +
            "    c.name AS courseName, " +
            "    base.average AS averageScore, " +
            "    base.cMax AS maxScore, " +
            "    base.cMin AS minScore, " +
            "    base.pass AS passCount, " +
            "    base.unPass AS unPassCount, " +
            "    AVG(POWER(scr.score - base.average, 2)) AS variance " +
            "FROM " +
            "    (SELECT " +
            "        cid, " +
            "        AVG(score) AS average, " +
            "        MAX(score) AS cMax, " +
            "        MIN(score) AS cMin, " +
            "        SUM(score >= 60) AS pass, " +
            "        SUM(score < 60) AS unPass " +
            "     FROM student_course_rel " +
            "     GROUP BY cid) AS base " +
            "JOIN student_course_rel scr ON base.cid = scr.cid " +
            "JOIN course c ON base.cid = c.id " +
            "GROUP BY base.cid, c.name, base.average, base.cMax, base.cMin, base.pass, base.unPass")
    List<ScoreDetails> getCourseScoreStats();
}
